<?php
//解析命令行参数
//例 php binlog_output.php --from_host=192.168.0.103 --from_user=dba  --from_pwd=wangYU0502 --from_port=3306 --start_time="2015-5-29 19:04:00" --end_time="2015-5-29 19:36:00"  --host=192.168.0.82 --user=dba --pwd=wangYU0502 --port=3306 --db=test 

$argv_arr=get_conf("db.conf");
//file_put_contents("1.txt",$argv_arr);
//$argv_arr=get_para($argv);
//file_put_contents("2.txt",$argv_arr);




//用户确认参数是否正确。
echo "Please confire your  parameters\nenter y continue,other cancel it\n \n";
print_array($argv_arr);
if(strtolower(trim(fgets(STDIN)))!="y")
{
	die("You have cancel it!\n");
}
//dump_array($argv_arr);
// 日志存储数据库链接信息
$host = $argv_arr['host'];
$user = $argv_arr['user'];
$pwd = $argv_arr['pwd'];
$port = $argv_arr['port'];
$db = $argv_arr['db'];
// 需要恢复得库
$from_host = $argv_arr['from_host'];
$from_user = $argv_arr['from_user'];
$from_pwd = $argv_arr['from_pwd'];
$from_port = $argv_arr['from_port'];
// 日志起止时间
$start_time=$argv_arr['start_time'];
$end_time=$argv_arr['end_time'];


// 定义换行符
$LF = "\n";

//临时目录当前目录的 tmpFILE
$tmpFILE="tmpFILE";
if(!is_dir($tmpFILE))
{
	mkdir($tmpFILE);
}

// 日志解析后的数据的文件名
//$binog_filename = "192.168.0.68_3306_1432883909959.sql";
$binog_filename = $tmpFILE.'/'."$from_host"."_"."$from_port"."_".getMillisecond().".sql";

//是否翻译字段标志位 trans_col_flag 
$trans_col_flag=true;

// 目标库存放日志表名	
$bin_table_name = "tab_binlog";
// 多少记录提交一次
$commit_num = 500;
// 多少个字符提交一次
$commit_size=5000;

// 定义初始化columns数组

$columns = get_init_columns($from_host, $from_user, $from_pwd, $from_port);
//dump_array($columns);

// 通过mysqlbinlog获取日志
if(!download_log($from_host,$from_port,$from_user,$from_pwd,$start_time,$end_time,$binog_filename))
{
	echo "get binlog failed! \n";
	die();
}else{
	echo "binlog put into file $binog_filename finished!\n";
}


// tab_binlog表检查，如果在目标库不存在就建
create_binlog_table();

//dump_array($columns);
analyze_binlog();
/**
 * sql语句挖取逻辑
 * 1 非row模式
 * 2 row模式
 * 
 * 处理方式：
1 在analyze_binlog中处理
2 对info单独分析
 */

function get_para($argv)
{
    
	$argv_arr=array();

	//定义解析规则
	$argv_rule=array(
	"from_host"=>'/^([0-9]+\.){3}[0-9]{1,3}$/',
	"from_user"=>'/\S+/',
	"from_pwd"=>'/\S+/',
	"from_port"=>'/^[0-9]+$/',
	"start_time"=>'/^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}$/',
	"end_time"=>'/^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}$/',
	"host"=>'/^([0-9]+\.){3}[0-9]{1,3}$/',
	"user"=>'/\S+/',
	"pwd"=>'/\S+/',
	"port"=>'/^[0-9]+$/',
	"db"=>'/\S+/'
	);

	$usage="example:\nphp binlog_output.php --from_host=192.168.0.103 --from_user=xxx  --from_pwd=xxx --from_port=3306 --start_time=\"2015-5-29 19:04:00\" --end_time=\"2015-5-29 19:36:00\"  --host=192.168.0.82 --user=xxx --pwd=xxx --port=3306 --db=test\n";

	foreach($argv_rule as $key1=>$value1){
		//echo $key." ".$value."\n";
		//检验规则
		//1 参数出现次数 必须一次
		//2 参数出现后=后面部分必须能满足正则表达式
		$result=array();
		foreach($argv as $key2=>$value2)
		{
			$pattern='/(?:--|-)'.$key1.'=(.*)/';
			if(preg_match($pattern,$value2,$matches))
			{
				//记录匹配的值
				//var_dump($matches);
				$result[]=$matches[1];
			}

		}
		
		//检查参数个数
		$count=count($result);
		if($count<1)
		{
			echo "please input the $key1 like --$key1=xxx \n";
			die($usage);
		}elseif($count>1)
		{
			echo "the $key1 have appear $count times \n";
			die($usage);
		}

		//检查值是否满足argv_rule

		if(!preg_match($value1,$result[0]))
		{	
			echo "value of $key1 is $result[0] is iggle\n";
			die($usage);
		}
		
		$argv_arr[$key1]=$result[0];

	 
	}

	return $argv_arr;
} 

function get_conf($file)
{
	//返回的数组
	$arr=array();
	//需要过滤的行
	$ignore_line_rule='/^(?:#|\s+)/';
	$f=fopen($file, 'r');
	if($f)
	{
		
		for($i = 1;!feof($f);$i++)
		{
			$line=fgets($f);
			if(!preg_match($ignore_line_rule,$line))
			{
				if(preg_match('/^(\w+)=([^\r\n]+)/',$line,$match))
				{
					//dump_array($match);
//					var_dump($match);
//					die();
					$arr[$match[1]]=$match[2];
				}
				else {
				    die("config file read error!\n");
				}
			}
		}
	}
	else{
		die("open config file faild!\n");
	}
 
	fclose($f);
	//给数组需要添加的元素添加引号
//	foreach($arr as $key=>$value)
//	{
//		if($key=="from_host" ||$key=="from_user" ||$key=="from_pwd" ||$key=="host" ||$key=="user" ||$key=="db")
//		{
//			$arr[$key]='"'.$value.'"';
//		}
//	}

	return $arr;
} 

function analyze_binlog() {
	//开始时间
	$start=getMillisecond();
	echo "start: ".current_time();
	global $binog_filename;
	global $commit_num;
	global $commit_size;
	global $from_host;
	global $from_port;

	$msg = fopen($binog_filename, 'r');
	$record_arr = array();
	if ($msg) {
		/**
		 * 日志格式分成三段
		 * 1 pos_start_line
		 * 2 pos_crc_line
		 * 3 pos_content
		 */
		$reg_str = '/# at \d{1,}/';
		$content = "";

		$record_count = 0; 
		// 初始化表tab_binlog的字段
		$log_name = $binog_filename;
		$pos = 0;
		$event_type = 'default';
		$server_id = 1;
		$end_log_pos = 0;
		$info = "";

		$start_time = "";
		$db_name = "";
		$sql_db_name = "";
		$table_name = "";
		$dml_type = "";
		$sql_text = array(); 
		// 执行sql截断字符
		$v_delimiter = ';'; 
		// 提交次数
		$commit_count = 0;
		// 提交的总记录数
		$sum=0;

		for($i = 1;!feof($msg);$i++) {
			$current_line = fgets($msg);
			$record_count++;
			if (preg_match($reg_str, $current_line)) {
				// 判断触发记录得到的结果
				if ($pos > 0) {
					//if ($pos == 228126528) {
					if ($sql_db_name != "") {
						$db_name = $sql_db_name;
					} 
					$sql_text = get_sql_from_info($event_type, $db_name, $table_name, $info, $v_delimiter);
					if (count($sql_text) < 2) {
						// echo "======================\n";
						// echo $log_name."\n";
						// echo $pos."\n";
						// echo $event_type."\n";
						// echo $server_id."\n";
						// echo $end_log_pos."\n";
						// echo $info."\n";
						// echo strlen($info);
						// echo $start_time;
						// echo	$db_name;
						// echo	$sql_db_name;
						// echo	$table_name;
						// echo	$dml_type;
						// echo $sql_text;
					} 
					// echo $sql_text[0];
					// binlog_put_mysql($log_name, $pos, $event_type, $server_id, $end_log_pos, $info, $start_time, $db_name, $table_name, $dml_type, $sql_text[0], $sql_text[1]);
					// 记录到数组
					$record_arr[] = "('$from_host','$from_port','$log_name',$pos,'$event_type',$server_id,$end_log_pos,'" . mysql_escape_string($info) . "','$start_time','$db_name','$table_name','$dml_type','" . mysql_escape_string($sql_text[0]) . "','" . mysql_escape_string($sql_text[1]) . "')"; 
					// var_dump($record_array);
					//}
				} 
				// 记录 pos
				$pos = str_replace("\r\n", "", substr($current_line, 5)); 
				// echo "start\n";
				// echo $pos."\n";
				// 重设 record_count
				$record_count = 1; 
				// 上次记录的info清空
				$info = ""; 
				// 初始化
				$start_time = "";
				$dml_type = "";
				$sql_db_name = "";
				$table_name = "";
				$sql_text = array();
			} else {
				if ($record_count == 2) {
					// 获取 server_id  end_log_pos
					if (preg_match('/#([\d: ]*) server id (\d)  end_log_pos (\d{1,}).CRC32 0x[\d\w]* \t([a-zA-Z_]+)/', $current_line, $regs)) {
						$start_time = $regs[1];
						$server_id = $regs[2];
						$end_log_pos = $regs[3];
						$event_type = $regs[4];
					} else {
						$start_time = "";
						$server_id = "";
						$end_log_pos = "";
						$event_type = "";
					} 
					// echo $server_id."\n";
					// echo "end\n";
					// echo $end_log_pos."\n";
					if ($event_type == 'Write_rows') {
						$dml_type = 'INSERT';
					} elseif ($event_type == 'Update_rows') {
						$dml_type = 'UPDATE';
					} elseif ($event_type == 'Delete_rows') {
						$dml_type = 'DELETE';
					} 
				} else {
					// 查看use db_name
					if ($record_count == 3) {
						if (preg_match('/use `?([a-zA-Z0-9_]*)`?/', $current_line, $regs)) {
							$db_name = $regs[1];
						} 
					} 
					// 检查dml_type
					if ($dml_type == "" || $table_name == "") {
						if (preg_match('/^(### )?(INSERT INTO |UPDATE |DELETE FROM )`?([a-zA-Z0-9_]*)`?\.?`?([a-zA-Z0-9_]*)`?/', $current_line, $regs)) {
							$dml_type = substr($regs[2], 0, 7); 
							// if($pos==3159350){
							// var_dump($regs);}
							if (empty($regs[4])) {
								$sql_db_name = "";
								$table_name = $regs[3];
							} else {
								$sql_db_name = $regs[3];
								$table_name = $regs[4];
							} 
						} 
					} 
					// 记录info
					$info .= $current_line;
				} 
			} 
			// 提交到数据库
			$c=count($record_arr);
			if ( $c== $commit_num || array_length($record_arr)>=$commit_size) {
				// echo "commit\n";
				binlog_put_mysql_arr($record_arr);
				$record_arr = array(); 
				//处理总量统计
				$sum+=$c;
				$commit_count++;
				if($commit_count<=10)
				{
					echo "speed : " . round($sum  * 1000 / (getMillisecond() - $start)) . "\n";
					
				}
			} 
			
		} 
		// 提交到数据库
		$sum+=count($record_arr);
		binlog_put_mysql_arr($record_arr);
		$record_arr = array();
	
		//结束时间
		echo "end  : ".current_time();	
		//处理记录总数
		echo "count: ".$sum."\n";
		//总耗时
		echo "use  : ".format_use_time(getMillisecond()-$start);
	} else {
		echo "打开文件失败!";
	} 

	fclose($msg);
	
} 

function trans_sql_array($arr, $type, $dml_type, $db, $table_name) {
	$result = array(); 
	// var_dump($arr);
	// echo $dml_type;
	// echo $table_name;
	// echo $table_name;
	if ($type == "format") {
		foreach($arr as $key => $value) {
			$result[$key] = get_fomart_sql($dml_type, $value, $db, $table_name);
		} 
	} elseif ($type == "reverse") {
		foreach($arr as $key => $value) {
			//echo $db.$table_name;
			$result[$key] = get_reverse_sql($dml_type, $value, $db, $table_name);
		} 
	} 

	return $result;
} 
/**
 * //正则表达式思路改成有限状态机
 * 
 * function get_fomart_sql($dml_type, $sql, $table_name) {
 * //	echo $dml_type;
 * //	echo $sql;
 * //	echo $table_name;
 * $result = ""; 
 * global $columns;
 * //var_dump($columns);
 * // 如果$table_name的字段初始化失败就不进行转换
 * if ($dml_type == "INSERT") {
 * // 添加逗号
 * $sql = preg_replace('/(@\d+=.*\n)/', '$1,', $sql); 
 * // 替换@1=		
 * //var_dump($columns["$table_name"]);
 * //var_dump($columns["$table_name"][0]);
 * $sql = preg_replace('/@(\d+)/', "$columns[$table_name][$1][$1]", $sql);
 * } elseif ($dml_type == "UPDATE") {
 * // 由 update set where 三部分组成
$sql = $sql . "\n"; //添加回车方便正则匹配处理  
 * // 1 WHERE AND SET 交换位置
 * $sql = preg_replace('/(UPDATE +`?[A-Za-z0-9_]+`?\.`?[A-Za-z0-9_]+`)\n( WHERE\n( *@\d+=.*\n)+)( SET\n( *@\d+=.*\n)+)/', "$1$4$2", $sql);
 * 
 * } elseif ($dml_type == "DELETE") {
 * // 添加 and
 * echo "here";
 * $sql = preg_replace('/(@\d+=.*\n)/', '$1 and ', $sql); 
 * echo $sql;
 * // 替换@1=
 * $sql = preg_replace('/@(\d+)/', 'aaaa$1', $sql); //此钟替换方式还不支持。。
echo $sql;
 * // =NULL 改成 is null
 * $sql = str_replace('=NULL', " IS NULL ", $sql);
 * } 
 * 
 * return $result;
 * }
 */

/**
 * 输入参数格式为基于ROW模式的sql
 * 例如
 * 
 * -- delete
 * DELTE FROM TEST.TT
 * WHERE
 * 1=A -> column_name=a and
 * 2=B -> column_name=b and
 * 3=C -> column_name=c and
 * 4=NULL -> column_name is null
 * 
 * --insert
 * INSERT INTO `test`.`tt`
 * SET
 * 1=12345 ->
 * 2='12345'->
 * 3=NULL ->
 * 1=1234511 -> column_name=12345 and
 * 2='1234511' -> column_name=12345 and
 * 3=NULL -> column_name is null
 * SET
 * 1=1234511
 * 2='bbbbbbb'
 * 3=null
 * 
 * //此功能暂时考虑放在数据库，避免逻辑变动重新插数
 * 
 * @column _name=12345,
 * @column _name=123456,
 * @column _name=NULL
 * 
 * --update
 * UPDATE `test`.`tt`
 * WHERE
 */

/**
 * 返回字符串的换行数
 */

function str_n_count($str) {
} 

/**
 * 读取字符串的第n行
 */

function str_n_content($str, $num) {
} 

function get_finsert_sql($sql, $tab_columns) {
	// echo $sql;
	//dump_array($tab_columns);
	//die();
	$result = "";
	$tmp = "";
	$char = "";
	$line = 0; //行号
	$len = strlen($sql);
	for($i = 0;$i < $len;$i++) {
		$char = substr($sql, $i, 1); //当前字符
		$tmp .= $char; //当前行字符
		if ($char == "\n" || $i == $len-1) {
			$line++; 
			// echo $line."\n";
			// 如果行号<3 直接记录 >=3进行转换
			if ($line < 3) {
				$result .= $tmp;
			} else {
				// 最后返回行组成 xx =
				// echo substr($tmp,0,strpos($tmp,'@'))."\n";
				// echo substr($tmp,strpos($tmp,'@'),strpos($tmp,'=')-strpos($tmp,'@'))."\n";
				// echo substr($tmp,strpos($tmp,'='))."\n";
				$result .= $tab_columns[substr($tmp, strpos($tmp, '@') + 1, strpos($tmp, '=') - strpos($tmp, '@')-1)-1].substr($tmp, strpos($tmp, '=')) . ',';
			} 

			$tmp = "";
		} 
	} 

	$result = substr($result, 0, strlen($result)-1); 
	// echo $result;
	return $result;
} 

function get_fdelete_sql($sql, $tab_columns) {
	// echo $sql;
	// var_dump($tab_columns);
	$result = "";
	$tmp = "";
	$char = "";
	$line = 0; //行号
	$len = strlen($sql);
	for($i = 0;$i < $len;$i++) {
		$char = substr($sql, $i, 1); //当前字符
		$tmp .= $char; //当前行字符
		if ($char == "\n" || $i == $len-1) {
			$line++; 
			// echo $line."\n";
			// 如果行号<3 直接记录 >=3进行转换
			if ($line < 3) {
				$result .= $tmp;
			} else {
				// 最后返回行组成 xx =
				// echo substr($tmp,0,strpos($tmp,'@'))."\n";
				// echo substr($tmp,strpos($tmp,'@'),strpos($tmp,'=')-strpos($tmp,'@'))."\n";
				// echo substr($tmp,strpos($tmp,'='))."\n";
				$tmp_new = $tab_columns[substr($tmp, strpos($tmp, '@') + 1, strpos($tmp, '=') - strpos($tmp, '@')-1)-1]. substr($tmp, strpos($tmp, '=')) . ' and '; 
				// echo $tmp_new;
				$result .= preg_replace('/=NULL/i', ' is null ', $tmp_new);
			} 

			$tmp = "";
		} 
	} 

	$result = substr($result, 0, strlen($result)-4); 
	// echo $result;
	return $result;
} 

function get_fupdate_sql($sql, $tab_columns) {
	// echo $sql;
	// var_dump($tab_columns);
	$result = "";
	$tmp = "";
	$char = "";
	$line = 0; //行号
	$len = strlen($sql); 
	// update语句用这三部分组成
	$str_update = "";
	$str_where = "";
	$str_set = ""; 
	// 探针变量
	$record_where = 0;
	$record_set = 0;

	for($i = 0;$i < $len;$i++) {
		$char = substr($sql, $i, 1); //当前字符
		$tmp .= $char; //当前行字符
		if ($char == "\n" || $i == $len-1) {
			$line++; 
			// echo $line."\n";
			// 如果行号<3 直接记录 >=3进行转换
			if ($line < 2) {
				$str_update .= $tmp;
			} else {
				if (trim($tmp) == 'WHERE') {
					$record_where = 1;
				} 

				if (trim($tmp) == 'SET') {
					$record_set = 1;
					$record_where = 0; 
					// 确定where部分
					$str_where = substr($str_where, 0, strlen($str_where)-4);
				} 

				if ($record_where == 1) {
					if (trim($tmp) != 'WHERE') {
						$tmp_new = $tab_columns[substr($tmp, strpos($tmp, '@') + 1, strpos($tmp, '=') - strpos($tmp, '@')-1)-1]. substr($tmp, strpos($tmp, '=')) . ' and '; 
						// echo $tmp_new;
						$str_where .= preg_replace('/=NULL/i', ' is null ', $tmp_new);
					} else {
						$str_where .= $tmp;
					} 
				} 

				if ($record_set == 1) {
					if (trim($tmp) != 'SET') {
						$tmp_new = $tab_columns[substr($tmp, strpos($tmp, '@') + 1, strpos($tmp, '=') - strpos($tmp, '@')-1)-1]. substr($tmp, strpos($tmp, '=')) . ','; 
						// echo $tmp_new;
						$str_set .= $tmp_new;
					} else {
						$str_set .= $tmp;
					} 
				} 

				if ($i == $len-1) {
					// 确定SET部分
					$str_set = substr($str_set, 0, strlen($str_set)-1); 
					// echo $str_set;
				} 
			} 

			$tmp = "";
		} 
	} 

	$result = $str_update . $str_set . $str_where; 
	// echo $result;
	return $result;
} 

function get_fomart_sql($dml_type, $sql, $db, $table_name) {
	global $columns;
	$result = ""; 
	// $table_name存在性判断
	//echo $db;
	//echo $table_name;
	//dump_array($columns[$db][$table_name]);

	if (array_key_exists($table_name, $columns[$db])) {
		if ($dml_type == "INSERT") {
			$result = get_finsert_sql($sql, $columns[$db][$table_name]);
		} elseif ($dml_type == "UPDATE") {
			$result = get_fupdate_sql($sql, $columns[$db][$table_name]);
		} elseif ($dml_type == "DELETE") {
			$result = get_fdelete_sql($sql, $columns[$db][$table_name]);
		} 
	} else {
		// 先直接 sql输出,后面可以考虑转换部分，column_name部分不替换
		$result = $sql;
	} 

	return $result;
} 

function get_rinsert_sql($sql, $table_name, $tab_columns) {
	// echo $sql;
	// var_dump($tab_columns);
	$result = "";
	$tmp = "";
	$char = "";
	$line = 0; //行号
	$len = strlen($sql);
	for($i = 0;$i < $len;$i++) {
		$char = substr($sql, $i, 1); //当前字符
		$tmp .= $char; //当前行字符
		if ($char == "\n" || $i == $len-1) {
			$line++; 
			// echo $line."\n";
			// 如果行号<3 直接记录 >=3进行转换
			if ($line < 3) {
				if ($line == 1) {
					// $result .= $tmp;
					// echo $result;
					$result = "delete from " . $table_name . " where "; 
					// echo $result;
				} 
			} else {
				$tmp_new = $tab_columns[substr($tmp, strpos($tmp, '@') + 1, strpos($tmp, '=') - strpos($tmp, '@')-1)-1]. substr($tmp, strpos($tmp, '=')) . ' and '; 
				// echo $tmp_new;
				$result .= preg_replace('/=NULL/i', ' is null ', $tmp_new);
			} 

			$tmp = "";
		} 
	} 

	$result = substr($result, 0, strlen($result)-4); 
	// echo $result;
	return $result;
} 

function get_rupdate_sql($sql, $table_name, $tab_columns) {
	// echo $sql;
	// var_dump($tab_columns);
	$result = "";
	$tmp = "";
	$char = "";
	$line = 0; //行号
	$len = strlen($sql); 
	// update语句用这三部分组成
	$str_update = "";
	$str_where = "";
	$str_set = ""; 
	// 探针变量
	$record_where = 0;
	$record_set = 0;

	for($i = 0;$i < $len;$i++) {
		$char = substr($sql, $i, 1); //当前字符
		$tmp .= $char; //当前行字符
		if ($char == "\n" || $i == $len-1) {
			$line++; 
			// echo $line."\n";
			// 如果行号<3 直接记录 >=3进行转换
			if ($line < 2) {
				$str_update .= $tmp;
			} else {
				if (trim($tmp) == 'WHERE') {
					$record_where = 1;
				} 

				if (trim($tmp) == 'SET') {
					$record_set = 1;
					$record_where = 0; 
					// 确定where部分
					// $str_where = substr($str_where, 0, strlen($str_where)-4);
				} 

				if ($record_where == 1) {
					if (trim($tmp) != 'WHERE') {
						$tmp_new = $tab_columns[substr($tmp, strpos($tmp, '@') + 1, strpos($tmp, '=') - strpos($tmp, '@')-1)-1]. substr($tmp, strpos($tmp, '=')) . ','; 
						// echo $tmp_new;
						$str_where .= preg_replace('/=NULL/i', ' is null ', $tmp_new);
					} else {
						$str_where .= " SET ";
					} 
				} 

				if ($record_set == 1) {
					if (trim($tmp) != 'SET') {
						$tmp_new = $tab_columns[substr($tmp, strpos($tmp, '@') + 1, strpos($tmp, '=') - strpos($tmp, '@')-1)-1]. substr($tmp, strpos($tmp, '=')) . ' and '; 
						// echo $tmp_new;
						$str_set .= $tmp_new;
					} else {
						$str_set .= " WHERE ";
					} 
				} 

				if ($i == $len-1) {
					// 确定SET部分
					// $str_set = substr($str_set, 0, strlen($str_set)-1);
					// echo $str_set;
				} 
			} 

			$tmp = "";
		} 
	} 

	$result = $str_update . substr($str_where, 0, strlen($str_where)-1) . substr($str_set, 0, strlen($str_set)-4); 
	// echo $result;
	return $result;
} 

function get_rdelete_sql($sql, $table_name, $tab_columns) {
	//echo $sql;
	//var_dump($tab_columns);
	$result = "";
	$tmp = "";
	$char = "";
	$line = 0; //行号
	$len = strlen($sql);
	for($i = 0;$i < $len;$i++) {
		$char = substr($sql, $i, 1); //当前字符
		$tmp .= $char; //当前行字符
		if ($char == "\n" || $i == $len-1) {
			$line++; 
			// echo $line."\n";
			// 如果行号<3 直接记录 >=3进行转换
			if ($line < 3) {
				if ($line == 1) {
					// $result .= $tmp;
					// echo $result;
					// var_dump($table_name);
					// echo $table_name;
					$result = "insert into  " . $table_name . " values ( "; 
					// echo $result;
				} 
			} else {
				$tmp_new = substr($tmp, strpos($tmp, '=') + 1) . ',';

				$result .= $tmp_new;
			} 

			$tmp = "";
		} 
	} 

	$result = substr($result, 0, strlen($result)-1) . ')'; 
	// echo $result;
	return $result;
} 

/**
 * 针对那些  Delete_rows || Update_rows ||Write_rows 生成逆向sql
 */

function get_reverse_sql($dml_type, $sql, $db, $table_name) {
	global $columns;
	$result = ""; 
	// $table_name存在性判断
	//echo $db;
	//echo $table_name; 
	//$data=var_export($columns[$db],TRUE);
	//file_put_contents("tmp2.txt",$data);
	if (array_key_exists($table_name, $columns[$db])) {
		if ($dml_type == "INSERT") {
			$result = get_rinsert_sql($sql, $table_name , $columns[$db][$table_name]);
		} elseif ($dml_type == "UPDATE") {
			$result = get_rupdate_sql($sql, $table_name, $columns[$db][$table_name]);
		} elseif ($dml_type == "DELETE") {
			//echo $dml_type.$sql.$db.$table_name."\n";
			$result = get_rdelete_sql($sql, $table_name, $columns[$db][$table_name]);
		} 
	} else {
		// 先直接 sql输出,后面可以考虑转换部分，column_name部分不替换
		$result = $sql;
	} 

	return $result;
} 
/**
 * 需要恢复数据的数据表的字段结构加载到内存
 */
function get_init_columns($host, $user, $pwd, $port) {
	$result = array();
	$db_array = array();

	$column_array = array();

	//拼接异常检查
	//1 2端都是确定字符没有问题 ok
	//2 任何一段有$host $port变量会有问题
	//3 先给变量赋值在拼接 ok
	//4 不是冒号问题 ok
	//5 echo 2个变量问题？
	//确定是传入参数问题 参数多了回车字符\r\n
//file_put_contents("para.txt",$host,FILE_APPEND);
//	file_put_contents("para.txt",$port,FILE_APPEND);
//	file_put_contents("para.txt","\n",FILE_APPEND);
//	file_put_contents("para.txt",$host.":".$port,FILE_APPEND);

	
	$con = mysql_connect($host.":".$port, $user, $pwd)
	or die ('conn error');
	$db_sql = "SELECT distinct lower(TABLE_SCHEMA) FROM information_schema.`COLUMNS` AS t"; 
	// echo $sql;
	$db_res = mysql_query($db_sql, $con);

	while ($db_row = mysql_fetch_row($db_res)) {
		$table_sql = "SELECT distinct lower(c.TABLE_NAME)  FROM information_schema.`COLUMNS` AS c WHERE  c.TABLE_SCHEMA='$db_row[0]'";
		$table_res = mysql_query($table_sql, $con);
		
		while ($table_row = mysql_fetch_row($table_res)) {
			$column_sql = "SELECT lower(c.COLUMN_NAME) COLUMN_NAME FROM information_schema.`COLUMNS` AS c WHERE c.TABLE_SCHEMA='$db_row[0]' and c.TABLE_NAME='$table_row[0]' order by c.ORDINAL_POSITION ";
			//die($column_sql);
			$column_res = mysql_query($column_sql, $con);
			$table_array = array();
			while ($column_row = mysql_fetch_assoc($column_res)) {
//				var_dump($db_row);
//				var_dump($table_row);
//				var_dump($column_row);
				//die();
				$table_array[] = $column_row['COLUMN_NAME'];
			} 
			//$db_array["$table_row[0]"] = $table_array;
			$result["$db_row[0]"]["$table_row[0]"] = $table_array;
		} 
		//$result["$db_row[0]"] = $db_array;
	} 
	// var_dump($result);
	return $result;
} 
function get_sql_from_info($event_type, $db, $table_name, $info, $v_delimiter) {
	$return = array();
	if ($event_type == "Xid" || $event_type == "Table_map" || $event_type == "Start" || $event_type == "Query" || $event_type == 'User_var') {
		$return[] = $info;
		$return[] = ""; 
		// var_dump($return);
	} elseif (($event_type == "Delete_rows" || $event_type == "Update_rows" || $event_type == "Write_rows") && strlen($info) > 0) {
		// 在insert|delete|update前增加回车
		// $return=preg_replace('/(### (?:(INSERT INTO|UPDATE|DELETE FROM)))/','\n$1',$info);
		// 提取sql
		if (preg_match_all('/((### (INSERT INTO|UPDATE|DELETE FROM) .*\n)(### +(?!\3).*\n?)*)/', $info, $regs, PREG_PATTERN_ORDER)) {
			// 加执行sql截断字符
			// 去###
			// var_dump($regs[1]);
			// 先不考虑sql_mode的影响 如果有重复的sql只取一条
			$dml_type = "";
			if ($event_type == "Delete_rows") {
				$dml_type = "DELETE";
			} elseif ($event_type == "Update_rows") {
				$dml_type = "UPDATE";
			} elseif ($event_type == "Write_rows") {
				$dml_type = "INSERT";
			} 
			// var_dump($regs[1]);
			$return[] = my_implode("\n" . $v_delimiter . "\n", trans_sql_array(array_unique(array_str_replace($regs[1], '###', "")), 'format', $dml_type, strtolower($db), strtolower($table_name)));
			$return[] = my_implode("\n" . $v_delimiter . "\n", trans_sql_array(array_unique(array_str_replace($regs[1], '###', "")), 'reverse', $dml_type, strtolower($db), strtolower($table_name))); 
			// $return=preg_replace("(###|\r|\n)","",implode(";",$regs[1]));
			// echo $return."\n";
		} ;
	} else {
		$return[] = $info;
		$return[] = "";
	} 
	// var_dump($return);
	return $return;
} 

//function binlog_put_mysql_one($log_name, $pos, $event_type, $server_id, $end_log_pos, $info, $start_time, $db_name, $table_name, $dml_type, $sql_text, $reverse_sql) {
//	global $host ;
//	global $port ;
//	global $user ;
//	global $pwd ;
//	global $db ;
//	global $bin_table_name;
//	$con = mysql_connect($host, $user, $pwd, $port)
//	or die ('Could not connect to the database server');
//	mysql_select_db($db, $con);
//	mysql_query("set names utf8");
//	//关闭sql日志记录
//	mysql_query("set sql_log_bin=0",$con);
//	
//	$sql = "insert into $bin_table_name values ('$log_name',$pos,'$event_type',$server_id,$end_log_pos,'" . mysql_escape_string($info) . "','$start_time','$db_name','$table_name','$dml_type','" . mysql_escape_string($sql_text) . "','" . mysql_escape_string($reverse_sql) . "')"; 
//	// mysql_escape_string特殊字符转移处理
//	// echo "==============\n\n\n\n";
//	// echo $sql;
//	mysql_query($sql, $con);
//	mysql_close($con);
//} 

function binlog_put_mysql_arr($arr) {
	global $host ;
	global $port ;
	global $user ;
	global $pwd ;
	global $db ;
	global $bin_table_name;
	$con = mysql_connect($host.":".$port, $user, $pwd)
	or die ('Could not connect to the database server');
	mysql_select_db($db, $con);
	mysql_query("set names utf8", $con); 

	//关闭sql日志记录
	mysql_query("set sql_log_bin=0",$con);
	// 优化参数
	mysql_query("SET INNODB_FLUSH_LOG_AT_TRX_COMMIT=0", $con);
	mysql_query("SET BULK_INSERT_BUFFER_SIZE=67108864", $con);
	mysql_query("SET MAX_ALLOWED_PACKET=100663296", $con);

	$sql = "insert into $bin_table_name values " . implode(',', $arr);
	
	//die($sql);
	// mysql_escape_string特殊字符转移处理
	// echo "==============\n\n\n\n";
	// echo $sql;
	mysql_query($sql, $con);
	mysql_close($con);

} 

function array_str_replace($arr, $src_str, $dst_str) {
	$result = array();
	foreach($arr as $key => $value) {
		$result[$key] = str_replace($src_str, $dst_str, $value);
	} 
	return $result;
} 

function my_implode($gap_char, $arr) {
	$res = "";
	foreach($arr as $key => $value) {
		$res .= $value . $gap_char;
	} 
	return $res;
} 

//数组字符长度总和

function array_length($arr)
{
	$len=0;
    //使用递归
	foreach($arr as $key=>$value)
	{
		//判断是否是数组
		if(is_array($value))
		{
			$len+=array_length($value);
		}else {
		    $len+=strlen($value);
		}

	}
	return $len;
}


function create_binlog_table() {
	global $host ;
	global $port ;
	global $user ;
	global $pwd ;
	global $db ;
	global $bin_table_name;
	$con = mysql_connect($host.":".$port, $user, $pwd)
	or die ('Could not connect to the database server');
	@mysql_query("set sql_log_bin=0", $con);
	mysql_select_db($db, $con);

	$sql = "CREATE TABLE if not exists $bin_table_name (
		  `host`	     varchar(30) not null,
		  `port`      varchar(6) not null,
		  `log_name` varchar(60) NOT null ,
		  `pos` bigint(20)  NOT null ,
		  `event_type` varchar(20) NOT null ,
		  `server_id` bigint(20) NOT null ,
		  `end_log_pos` bigint(20)  NOT null ,
		  `info` LONGTEXT NOT null ,
		  `start_time` varchar(45)  NOT null ,
		  `db_name` varchar(45)  NOT null ,
		  `table_name` varchar(45)  NOT null ,
		  `sql_type` varchar(10)  NOT null ,
		  `sql_text` LONGTEXT NOT null ,
		  `reverse_sql` LONGTEXT NOT null 
		) ENGINE=CSV DEFAULT CHARSET=utf8 
		COMMENT='binlog日志临时存储表'";

	@mysql_query($sql, $con);

	mysql_close($con);
} 
function getMillisecond() {
	list($t1, $t2) = explode(' ', microtime());
	return (float)sprintf('%.0f', (floatval($t1) + floatval($t2)) * 1000);
} 

function dump_array($arr)
{
	$data=var_export($arr,TRUE);
	file_put_contents("tmp.txt",$data);
	die();
} 
function print_array($arr)
{
    foreach($arr as $key=>$value)
	{
		echo "$key=$value\n";
	}
} 

function current_time()
{
	date_default_timezone_set('Etc/GMT-8'); 
	
	return date("Y-m-d H:i:s")."\n";
    
} 
function current_time_seq()
{
	date_default_timezone_set('Etc/GMT-8'); 
	
	return date("YmdHis")."\n";
    
} 


// $use_time单位毫秒
function format_use_time($use_time)
{
    //显示 小时 分 秒 0:0:10.123
	return floor($use_time/3600000).":".floor(($use_time%3600000)/60000).":".floor(($use_time%60000)/1000).".".floor($use_time%1000);
}


//echo download_log("192.168.0.66","3306","dba","wangYU0502","2015-04-28 9:00:00","2015-04-28 15:05:00",'123.sql');

function download_log($host,$port,$user,$pass,$start_time,$end_time,$file_name)
{
    //参数检查 先只考虑检查时间（比较容易出错）todo
	//查找 binary logs
	$flag=false;

	$sql="SHOW BINARY LOGS";
	$con = mysql_connect($host.":".$port, $user, $pass, $port)
	or die ('Could not connect to the database server');
	$res=mysql_query($sql, $con);
	$log_str="";
	while($row=mysql_fetch_row($res))
	{	
		$log_str.=$row[0]." ";
	}		
	mysql_close($con);

	//die($log_str);
	//如果处理通过，修改finish_flag
	$cmd="mysqlbinlog  --read-from-remote-server -h$host -u$user -p$pass -P$port  $log_str  --start-datetime=\"$start_time\" --stop-datetime=\"$end_time\"  --base64-output=DECODE-ROWS -v >$file_name";
	//die($cmd);

	exec($cmd,$output,$status);
	
	$flag=$status?false:true;

	return $flag;
} 

?>